package db;

import beans.MaintenanceBean;

//import util.DBUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class MaintenanceDAO extends MySQLConnectionBean {
    private MySQLConnectionBean connection;

    public MaintenanceDAO() {
        connection = new MySQLConnectionBean();
    }

    /**
     * 添加维修记录
     * @param maintenance 维修记录对象
     * @return 是否添加成功
     */
    public boolean addMaintenance(MaintenanceBean maintenance) throws SQLException {
        String sql = "INSERT INTO maintenance (maintenance_id, VehicleID, status, cost, maintenance_time, reviewer, DriverID) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = connection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, maintenance.getMaintenanceId());
            stmt.setInt(2, maintenance.getVehicleID());
            stmt.setString(3, maintenance.getStatus());
            stmt.setBigDecimal(4, maintenance.getCost());
            stmt.setTimestamp(5, new Timestamp(maintenance.getMaintenanceTime().getTime()));
            stmt.setString(6, maintenance.getReviewer());
            stmt.setInt(7, maintenance.getDriverID());

            return stmt.executeUpdate() > 0;
        }
    }

    /**
     * 根据ID获取维修记录（包含车牌号和司机姓名）
     * @param maintenanceId 维修记录ID
     * @return 维修记录对象
     */
    public MaintenanceBean getMaintenanceById(int maintenanceId) throws SQLException {
        String sql = "SELECT m.*, v.license_plate, d.DriverName " +
                "FROM maintenance m " +
                "LEFT JOIN vehicle v ON m.VehicleID = v.VehicleID " +
                "LEFT JOIN drivers d ON m.DriverID = d.DriverID " +
                "WHERE m.maintenance_id = ?";

        try (Connection conn = connection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, maintenanceId);

            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return mapResultSetToMaintenance(rs);
                }
            }
        }
        return null;
    }

    /**
     * 获取所有维修记录（包含车牌号和司机姓名）
     * @return 维修记录列表
     */
    public List<MaintenanceBean> getMaintenanceRecords() throws SQLException {
        List<MaintenanceBean> records = new ArrayList<>();
        String sql = "SELECT m.*, v.license_plate, d.DriverName " + // 使用正确的列名
                "FROM maintenance m " +
                "LEFT JOIN vehicle v ON m.VehicleID = v.VehicleID " +
                "LEFT JOIN drivers d ON m.DriverID = d.DriverID";

        try (Connection conn = connection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                records.add(mapResultSetToMaintenance(rs));
            }
        }
        return records;
    }

    /**
     * 根据条件搜索维修记录
     * @param plateNumber 车牌号
     * @param status 状态
     * @param startDate 开始日期
     * @param endDate 结束日期
     * @param driver 司机姓名
     * @return 符合条件的维修记录列表
     */
    public List<MaintenanceBean> searchMaintenanceRecords(String plateNumber, String status,
                                                          String startDate, String endDate, String driver) throws SQLException {
        List<MaintenanceBean> records = new ArrayList<>();
        StringBuilder sql = new StringBuilder(
                "SELECT m.*, v.license_plate, d.DriverName " + // 使用正确的列名
                        "FROM maintenance m " +
                        "LEFT JOIN vehicle v ON m.VehicleID = v.VehicleID " +
                        "LEFT JOIN drivers d ON m.DriverID = d.DriverID " +
                        "WHERE 1=1"
        );

        List<Object> params = new ArrayList<>();

        // 车牌号条件
        if (plateNumber != null && !plateNumber.isEmpty()) {
            sql.append(" AND v.license_plate LIKE ?");
            params.add("%" + plateNumber + "%");
        }

        // 状态条件
        if (status != null && !status.isEmpty()) {
            sql.append(" AND m.status = ?");
            params.add(status);
        }

        // 开始日期条件
        if (startDate != null && !startDate.isEmpty()) {
            sql.append(" AND DATE(m.maintenance_time) >= ?");
            params.add(Date.valueOf(startDate));
        }

        // 结束日期条件
        if (endDate != null && !endDate.isEmpty()) {
            sql.append(" AND DATE(m.maintenance_time) <= ?");
            params.add(Date.valueOf(endDate));
        }

        // 司机姓名条件
        if (driver != null && !driver.isEmpty()) {
            sql.append(" AND d.DriverName LIKE ?");
            params.add("%" + driver + "%");
        }

        // 添加排序
        sql.append(" ORDER BY m.maintenance_time DESC");

        try (Connection conn = connection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql.toString())) {

            // 设置参数
            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    records.add(mapResultSetToMaintenance(rs));
                }
            }
        }
        return records;
    }

    /**
     * 根据车辆ID获取维修记录
     * @param vehicleId 车辆ID
     * @return 该车辆的维修记录列表
     */
    public List<MaintenanceBean> getMaintenancesByVehicle(int vehicleId) throws SQLException {
        List<MaintenanceBean> records = new ArrayList<>();
        String sql = "SELECT m.*, v.license_plate, d.DriverName " +
                "FROM maintenance m " +
                "LEFT JOIN vehicle v ON m.VehicleID = v.VehicleID " +
                "LEFT JOIN Drivers d ON m.DriverID = d.DriverID " +
                "WHERE m.VehicleID = ?";

        try (Connection conn = connection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, vehicleId);

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    records.add(mapResultSetToMaintenance(rs));
                }
            }
        }
        return records;
    }

    /**
     * 更新维修记录状态
     *
     * @param maintenanceId 维修记录ID
     * @param status        新状态
     * @param reviewer
     * @return 是否更新成功
     */
    public boolean updateMaintenanceStatus(int maintenanceId, String status, String reviewer)
            throws SQLException {
        String sql = "UPDATE maintenance SET status = ?, reviewer = ?WHERE maintenance_id = ?";

        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, status);
            stmt.setString(2, reviewer); // 使用实际审核人
            stmt.setInt(3, maintenanceId);

            return stmt.executeUpdate() > 0;
        }
    }

    /**
     * 删除维修记录
     * @param maintenanceId 维修记录ID
     * @return 是否删除成功
     */
    public boolean deleteMaintenance(int maintenanceId) throws SQLException {
        String sql = "DELETE FROM maintenance WHERE maintenance_id = ?";

        try (Connection conn = connection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, maintenanceId);
            return stmt.executeUpdate() > 0;
        }
    }

    /**
     * 根据司机ID获取维修记录
     * @param driverId 司机ID
     * @return 该司机相关的维修记录列表
     */
    public List<MaintenanceBean> getMaintenancesByDriver(int driverId) throws SQLException {
        List<MaintenanceBean> records = new ArrayList<>();
        String sql = "SELECT m.*, v.license_plate, d.DriverName " +
                "FROM maintenance m " +
                "LEFT JOIN vehicle v ON m.VehicleID = v.VehicleID " +
                "LEFT JOIN Drivers d ON m.DriverID = d.DriverID " +
                "WHERE m.DriverID = ?";

        try (Connection conn = connection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, driverId);

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    records.add(mapResultSetToMaintenance(rs));
                }
            }
        }
        return records;
    }

    /**
     * 根据状态获取维修记录
     * @param status 状态值
     * @return 符合状态的维修记录列表
     */
    public List<MaintenanceBean> getMaintenancesByStatus(String status) throws SQLException {
        List<MaintenanceBean> records = new ArrayList<>();
        String sql = "SELECT m.*, v.license_plate, d.DriverName " +
                "FROM maintenance m " +
                "LEFT JOIN vehicle v ON m.VehicleID = v.VehicleID " +
                "LEFT JOIN Drivers d ON m.DriverID = d.DriverID " +
                "WHERE m.status = ?";

        try (Connection conn = connection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, status);

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    records.add(mapResultSetToMaintenance(rs));
                }
            }
        }
        return records;
    }



    /**
     * 将ResultSet映射为Maintenance对象
     * @param rs 查询结果集
     * @return 维修记录对象
     */
    private MaintenanceBean mapResultSetToMaintenance(ResultSet rs) throws SQLException {
        MaintenanceBean maintenance = new MaintenanceBean();
        maintenance.setMaintenanceId(rs.getInt("maintenance_id"));
        maintenance.setVehicleID(rs.getInt("VehicleID"));
        maintenance.setStatus(rs.getString("status"));
        maintenance.setCost(rs.getBigDecimal("cost"));
        maintenance.setMaintenanceTime(rs.getTimestamp("maintenance_time"));
        maintenance.setReviewer(rs.getString("reviewer"));
        maintenance.setDriverID(rs.getInt("DriverID"));
        maintenance.setPurpose(rs.getString("purpose"));
        maintenance.setLicensePlate(rs.getString("license_plate"));
        maintenance.setDriverName(rs.getString("DriverName")); // 使用正确的列名
        return maintenance;
    }
}